package com.zhangyj.impl.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zhangyj.dao.ClientDao;
import com.zhangyj.db.DBUtil;
import com.zhangyj.entitis.Client;
import com.zhangyj.entitis.ClientType;
import com.zhangyj.util.Pagination;

public class ClientDaoImpl implements ClientDao {

	public List<ClientType> getTypes() throws SQLException {
		List<ClientType> list = new ArrayList<ClientType>();
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			String sql = "SELECT hylh,hylm FROM kehufl";
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rst = ps.executeQuery();
			while (rst.next()) {
				ClientType pt = new ClientType(rst.getString(1), rst.getString(2));
				list.add(pt);
			}
			rst.close();
			ps.close();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
		return list;
	}

	public ClientType loadType(String hylh) throws SQLException {
		Connection conn = null;
		ClientType pt = null;
		try {
			conn = DBUtil.getConnection();
			String sql = "SELECT hylm FROM kehufl WHERE hylh=? ";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, hylh);
			ResultSet rst = ps.executeQuery();
			while (rst.next()) {
				pt = new ClientType(hylh, rst.getString(1));
			}
			rst.close();
			ps.close();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
		return pt;
	}

	public void delType(String typeId) throws SQLException {
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			String sql = "DELETE FROM kehufl WHERE hylh=? ";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, typeId);
			ps.executeUpdate();
			ps.close();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	public void saveType(ClientType ct, boolean isUpdate) throws SQLException {
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			String sql = "INSERT INTO kehufl (hylh,hylm)VALUES(?,?)";
			if (isUpdate) {
				sql = "UPDATE kehufl SET hylm=? WHERE hylh=?";
			}

			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, ct.getHylm());
			ps.setString(2, ct.getHylh());
			ps.execute();
			ps.close();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}
	public int getClientCount( String con) throws SQLException {
		int count = 0;
		boolean hasParam = con != null && con.trim().length() > 0;
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			StringBuilder sql = new StringBuilder();
			sql.append("SELECT COUNT(*) FROM kehu a");
			if (hasParam) {
				sql.append(" WHERE a.khxm like ?");
			}
			PreparedStatement ps = conn.prepareStatement(sql.toString());
			if (hasParam) {
				ps.setString(1, "%" + con + "%");
			}
			ResultSet rst = ps.executeQuery();
			if (rst.next()) {
				count = rst.getInt(1);
			}
			rst.close();
			ps.close();

		} finally {
			if (conn != null)
				conn.close();
		}
		return count;
	}
	public List<Client> getClientList(Pagination p, String con) throws SQLException {
		List<Client> list = new ArrayList<Client>();
		boolean hasParam = con != null && con.trim().length() > 0;
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			StringBuilder sql = new StringBuilder();
			sql.append("SELECT a.khbh, a.khxm, a.khdz, a.lxdh, a.dzyj, a.lrry, a.khzy,a.zhye, a.jbs, a.jfs,b.yhm as lrryname,c.yhm as khzyname,a.Zhye,a.Jfs,a.khdz FROM kehu a");
			sql.append(" LEFT JOIN userinfo b ON a.lrry=b.userid");
			sql.append(" LEFT JOIN userinfo c ON a.khzy=c.userid");
			if (hasParam) {
				sql.append(" WHERE a.khxm like ? or a.lxdh like ?");
			}
			PreparedStatement ps = conn.prepareStatement(p.builderSql(sql.toString(), "khbh", "a", false));
			if (hasParam) {
				ps.setString(1, "%" + con + "%");
				ps.setString(2, "%" + con + "%");
			}
			ResultSet rst = ps.executeQuery();
			while (rst.next()) {
				Client client = new Client();
				client.setKhbh(rst.getString(1));
				client.setKhxm(rst.getString(2));
				client.setKhdz(rst.getString(3));
				client.setLxdh(rst.getString(4));
				client.setDzyj(rst.getString(5));
				client.setLrry(rst.getInt(6));
				client.setKhzy(rst.getInt(7));
				client.setZhye(rst.getBigDecimal(8));
				client.setJbs(rst.getBigDecimal(9));
				client.setJfs(rst.getBigDecimal(10));
				client.setLrryname(rst.getString(11));
				client.setKhzyname(rst.getString(12));
				client.setZhye(rst.getBigDecimal(13));
				client.setJfs(rst.getBigDecimal(14));
				String dz = rst.getString(15);
				if (dz != null && dz.length() > 15) {
					client.setKhdz(dz.substring(0, 15)+"...");
				} else {
					client.setKhdz(dz);
				}
				list.add(client);
				
			}
			rst.close();
			ps.close();

		} finally {
			if (conn != null)
				conn.close();
		}
		return list;
	}

	public Client loadClient(String khbh) throws SQLException {
		Client client = new Client();
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			StringBuilder sql = new StringBuilder();
			sql.append("SELECT a.khbh, a.khxm, a.khdz, a.lxdh, a.dzyj, a.lrry, a.khzy,a.zhye, a.jbs, a.jfs,b.yhm as lrryname,c.yhm as khzyname,a.hylh,d.hylm FROM kehu a");
			sql.append(" LEFT JOIN userinfo b ON a.lrry=b.userid");
			sql.append(" LEFT JOIN userinfo c ON a.khzy=c.userid");
			sql.append(" LEFT JOIN kehufl d ON a.hylh = d.hylh");
			sql.append(" WHERE a.khbh=?");
			PreparedStatement ps = conn.prepareStatement(sql.toString());
			ps.setString(1, khbh);
			ResultSet rst = ps.executeQuery();
			while (rst.next()) {
				client.setKhbh(rst.getString(1));
				client.setKhxm(rst.getString(2));
				client.setKhdz(rst.getString(3));
				client.setLxdh(rst.getString(4));
				client.setDzyj(rst.getString(5));
				client.setLrry(rst.getInt(6));
				client.setKhzy(rst.getInt(7));
				client.setZhye(rst.getBigDecimal(8));
				client.setJbs(rst.getBigDecimal(9));
				client.setJfs(rst.getBigDecimal(10));
				client.setLrryname(rst.getString(11));
				client.setKhzyname(rst.getString(12));
				client.setHylh(rst.getString(13));
				client.setHylm(rst.getString(14));
			}
			rst.close();
			ps.close();

		} finally {
			if (conn != null)
				conn.close();
		}
		return client;
	}

	public void save(Client client, boolean isUpdate) throws SQLException {
		Connection dbc = null;
		try {
			dbc = DBUtil.getConnection();
			DBUtil.begnTrans();
			if (isUpdate) {
				String sql = "DELETE FROM kehu WHERE khbh=?";
				PreparedStatement ps = dbc.prepareStatement(sql);
				ps.setString(1, client.getKhbh());
				ps.execute();
				ps.close();
			}
			String sql = "INSERT INTO kehu(khbh, khxm, khdz, lxdh, dzyj, lrry, khzy,zhye, jbs, jfs,hylh)VALUES(?,?,?,?,?, ?,?,?,?,?,?)";
			PreparedStatement ps = dbc.prepareStatement(sql);
			ps.setString(1, client.getKhbh());
			ps.setString(2, client.getKhxm());
			ps.setString(3, client.getKhdz());
			ps.setString(4, client.getLxdh());
			ps.setString(5, client.getDzyj());
			ps.setInt(6, client.getLrry());
			ps.setInt(7, client.getKhzy());
			ps.setBigDecimal(8, client.getZhye());
			ps.setBigDecimal(9, client.getJbs());
			ps.setBigDecimal(10, client.getJfs());
			ps.setString(11, client.getHylh());
			ps.execute();
			ps.close();
			DBUtil.commit();
		} catch (SQLException e) {
			if (dbc != null) {
				DBUtil.rollback();
			}
			throw e;
		} finally {
			if (dbc != null) {
				dbc.close();
			}
		}
	}

	public void del(String khbh) throws SQLException {
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			String sql = "DELETE FROM kehu WHERE khbh=?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, khbh);
			ps.execute();
			ps.close();
		} catch (SQLException e) {
			throw e;
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}
	public void fpzy(String khbh,int userid) throws SQLException {
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			String sql = "UPDATE kehu SET Khzy=? WHERE khbh=?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, userid);
			ps.setString(2, khbh);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException e) {
			throw e;
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}
}
